package person.bing.fbcloud.common.core.util;

import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import person.bing.fbcloud.common.core.annotation.Excel;
import person.bing.fbcloud.common.core.exception.CheckedException;

import java.io.*;
import java.lang.reflect.Field;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * excel工具类
 * @author: mengfanbing
 * @date: 2019/7/16
 */
public class ExcelUtil<T>{
//    private static final long serialVersionUID = 1L;
//    private static final String PATH = "D:"+separator+"file"+separator;
    private static String[] parsePatterns = {
            "yyyy-MM-dd","yyyy-MM-dd HH:mm:ss","yyyy-MM-dd HH:mm","yyyy-MM",
            "yyyy/MM/dd","yyyy/MM/dd HH:mm:ss","yyyy/MM/dd HH:mm","yyyy/MM",
            "yyyy.MM.dd","yyyy.MM.dd HH:mm:ss","yyyy.MM.dd HH:mm","yyyy.MM",
            };
    private Class<T> clazz;

    public ExcelUtil(Class<T> clazz){
        this.clazz = clazz;
    }

    /**
     * 导出工作簿模板
     * @param localPath 工作簿模板导出路径
     * @return
     */
    public String exportExcel(String localPath){
        return this.exportExcel(localPath,new ArrayList<>());
    }

    /**
     * 导出工作簿
     * @param localPath 工作簿导出路径
     * @param  list 填充excel的数据列表
     * @return
     */
    public String exportExcel(String localPath,List<T> list){
        String workbookName= null;
        List<Field> fieldList = new ArrayList<>();
        Field[] declaredFields = clazz.getDeclaredFields();
        int listSize = list.size();
        if (declaredFields != null && declaredFields.length>0){
            Map<String,Integer> map = new HashMap<>();
            int length = declaredFields.length;
            for (int i=0;i<length;i++){
                Field field = declaredFields[i];
                Excel excel = field.getAnnotation(Excel.class);
                boolean present = declaredFields[i].isAnnotationPresent(Excel.class);
                if (present){
                    fieldList.add(field);
                }
            }
            SXSSFWorkbook workbook = new SXSSFWorkbook();
            SXSSFSheet sheet = workbook.createSheet();
            SXSSFRow row = sheet.createRow(0);

            int fieldSize = fieldList.size();
            //填充表头
            for (int i=0;i<fieldSize;i++){
                Field field = fieldList.get(i);
//                Class<?> type = field.getType();
                String name = field.getName();
                String value = fieldList.get(i).getAnnotation(Excel.class).value();
                row.createCell(i).setCellValue(value);
                map.put(name,i);


            }
            //如果数据列表的长度大于0，填充数据
            if (listSize > 0){
                for (int j=0;j<listSize;j++){
                    SXSSFRow sheetRow = sheet.createRow(j + 1);
                    for (int i=0;i<fieldSize;i++){
                        try {
                            Field field = fieldList.get(i);
                            field.setAccessible(true);
                            Object o = field.get((T) list.get(j));
                            if (ObjectUtil.isNotNull(o)){
                                if (field.getType() == LocalDateTime.class){
                                    o = o.toString().replace("T", " ");
                                }
                                sheetRow.createCell(i).setCellValue(o.toString());
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }

            workbookName = this.createFilePath(localPath,IdUtil.simpleUUID());
            try (OutputStream outputStream = new FileOutputStream(workbookName);){
                workbook.write(outputStream);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return workbookName;
    }

    /**
     * 生成工作簿路径
     * @param localPath 文件父路径
     * @param workbookName 工作簿名称
     * @return
     */
    public String createFilePath(String localPath,String workbookName){
        String path = localPath + workbookName + ".xlsx";
        File file = new File(path);
        if (!file.getParentFile().exists()){
            file.getParentFile().mkdirs();
        }
        return path;
    }

    /**
     * 导入excel
     * @param is 输入流
     * @return 实体类List
     * @throws Exception
     */
    public List<T> importExcel(InputStream is) throws Exception {
        return this.importExcel(null,is);
    }

    /**
     * 导入excel
     * @param sheetName sheet名称
     * @param is 输入流
     * @return 实体类List
     * @throws Exception
     */
    public List<T> importExcel(String sheetName,InputStream is) throws Exception {
        List<T> tList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = null;
        if (StrUtil.isNotEmpty(sheetName)){
            sheet = wb.getSheet(sheetName);
        }else {
         sheet = wb.getSheetAt(0);
        }
        if (sheet == null){
            throw new CheckedException("文件sheet不存在");
        }
        int numberOfRows = sheet.getPhysicalNumberOfRows();
        if (numberOfRows > 0){
            Row firstRow = sheet.getRow(0);
            int numberOfCells = firstRow.getPhysicalNumberOfCells();
            Map<String,Integer> map = new HashMap<>();
            Field[] declaredFields = clazz.getDeclaredFields();
            int fieldNum = declaredFields.length;
            for (int i= 0;i<numberOfCells;i++){
                map.put(firstRow.getCell(i).getStringCellValue(),i);
            }
            for (int i=1;i<numberOfRows;i++){
                Object cellValue = null;
                Row row = sheet.getRow(i);
                T t = clazz.newInstance();
                for (Field field:declaredFields){
                    field.setAccessible(true);//私有属性设置可访问
                    String name = field.getName();
                    Excel excel = field.getAnnotation(Excel.class);
                    if (ObjectUtil.isNotNull(excel) && ObjectUtil.isNotNull(excel.value())){
                        Integer colNum = map.get(excel.value());
                        if (ObjectUtil.isNotNull(colNum)){//保证实体类中注解的字段在excel中可以没有
                            cellValue = this.getCellvalue(row, colNum);
                            if (LocalDate.class == field.getType()){
                                cellValue = LocalDate.parse(cellValue.toString(), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                            }if (LocalDateTime.class == field.getType()){
                                cellValue = LocalDateTime.parse(cellValue.toString(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                            }if (LocalTime.class == field.getType()){
                                cellValue = LocalTime.parse(cellValue.toString(),DateTimeFormatter.ofPattern("HH:mm:ss"));
                            }
                            ReflectUtil.setFieldValue(t,name,cellValue);
                        }
                    }
                }
                tList.add(t);
            }
        }
        return tList;
    }

    /**
     * 获取指定行的指定元素
     * @param row 行
     * @param colNum 列的序号
     * @return 单元格里的值
     */
    public Object getCellvalue(Row row,int colNum){
        Object cellValue = null;
        if (row != null && row.getCell(colNum) != null){
            Cell cell = row.getCell(colNum);
            CellType cellTypeEnum = cell.getCellTypeEnum();
            if (CellType.NUMERIC.equals(cellTypeEnum)){
                cellValue = row.getCell(colNum).getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell)){
                    cellValue = DateUtil.getJavaDate((Double)cellValue);//poi excel 日期格式转换
                }
            }else if (CellType.STRING.equals(cellTypeEnum)){
                cellValue = row.getCell(colNum).getStringCellValue();
            }else if (CellType.BOOLEAN.equals(cellTypeEnum)){
                cellValue = row.getCell(colNum).getBooleanCellValue();
            }else if (CellType.ERROR.equals(cellTypeEnum)){
                cellValue = row.getCell(colNum).getErrorCellValue();
            }
        }
        return cellValue;
    }
}
